package com.geekathon.mobzilla.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.geekathon.mobzilla.model.ExperienceAnalysis;
import com.geekathon.mobzilla.model.User;

public class LoadBalancingStatistics {

	private static Connection con = null;
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:9129/test", "dba", "dba");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static List<User> getTopUsers(int number) {
		List<User> userList = new ArrayList<User>();

		try {
			PreparedStatement stmt = con
					.prepareStatement("select count(distinct id) as 'UserCount', assignedto from buginfo "
							+ "where status not in ('RESOLVED') group by assignedto order by UserCount desc limit ?;");
			stmt.setInt(1, number);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("UserCount"));
				user.setName(rs.getString("assignedto"));
				userList.add(user);
			}
			rs.close();
		} catch (SQLException e) {
			// do nothing
		}
		return userList;

	}

	public static List<User> getWorstUsers(int number) {
		List<User> userList = new ArrayList<User>();

		try {
			PreparedStatement stmt = con
					.prepareStatement("select count(distinct id) as 'UserCount', assignedto from buginfo "
							+ "where status not in ('RESOLVED') group by assignedto order by UserCount asc limit ?;");
			stmt.setInt(1, number);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("UserCount"));
				user.setName(rs.getString("assignedto"));
				userList.add(user);
			}
			rs.close();
		} catch (SQLException e) {
			// do nothing
		}
		return userList;

	}

	public static List<ExperienceAnalysis> getExperienceFactor() {

		List<ExperienceAnalysis> list = new ArrayList<ExperienceAnalysis>();

		try {
			PreparedStatement stmt = con
					.prepareStatement("select max(tt.count) as 'count', tt.component, tt.assignedto from "
							+ "(select count(*) as 'count', component, assignedto from buginfo b	"
							+ "group by component,assignedto) tt group by tt.component;");
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				ExperienceAnalysis analysis = new ExperienceAnalysis();
				analysis.setAssignedTo(rs.getString("assignedto"));
				analysis.setComponent(rs.getString("component"));
				analysis.setCount(rs.getInt("count"));
				list.add(analysis);
			}
			rs.close();
		} catch (SQLException e) {
			// do nothing
		}
		return list;

	}
}